package com.wy.framework.quartz;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.FlushMode;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.wy.business.developer.developer.services.RegistService;
import com.wy.component.dataConvert.DateUtil;
import com.wy.component.email.SendEmailCode;
import com.wy.component.file.FileUtil;
import com.wy.component.properties.PropertiesUtil;
import com.wy.component.wxUtils.MGroup;
import com.wy.component.wxUtils.MPerson;
import com.wy.component.wxUtils.MTag;
import com.wy.component.wxUtils.ParamesAPI;
import com.wy.component.wxUtils.StringUtil;
import com.wy.component.wxUtils.WeixinUtil;
import com.wy.framework.constants.Constants;
import com.wy.framework.constants.DBConstant;
import com.wy.framework.dao.ErrorSqlException;
import com.wy.framework.dao.IDataBaseDao;
import com.wy.framework.pojos.TSysEmailmq;
import com.wy.framework.pojos.TSysOrginfo;
import com.wy.framework.pojos.TSysOrgroles;
import com.wy.framework.pojos.TSysRoleinfo;
import com.wy.framework.pojos.TSysUserinfo;
import com.wy.framework.pojos.TSysUserroles;

public class QuartzService {
    private static Logger logger = LoggerFactory.getLogger(QuartzService.class);
	@Autowired
	private IDataBaseDao dataBaseDao;
	@Autowired
	private RegistService registService;
	@Autowired
	private JdbcTemplate jdbcTemplateSLMiddle;
	
	static PropertiesUtil p=new PropertiesUtil();

	Session session;
	Transaction transaction;

	/**
	 * 开始事务
	 */
	private void beginTransaction() {
		session = getDataBaseDao().getHibernateTemplate().getSessionFactory().openSession();
//		if(transaction==null || !transaction.isActive() || transaction.wasCommitted() || transaction.wasRolledBack())
		session.setFlushMode(FlushMode.AUTO);
		transaction=session.beginTransaction();
	}

	/**
	 * 关闭事务
	 * @param isSuccess
	 */
	private void endTransaction(boolean isSuccess) {
		if(!isSuccess){
			transaction.rollback();
			return;
		}
		session.flush();
		session.clear();
		transaction.commit();
		session.close();
	}
	
    /**
     * Function Name               saveSyncUserSLSDInfo                                   
     * @throws URISyntaxException
     * @throws IOException          		             
     * @throws SQLException 
     * @description 同步基础数据到水利,中间库到本地库
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-19 下午5:40:28           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused" })
	private void saveSyncUserSLSDInfo() throws URISyntaxException, IOException, SQLException {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户开始oracle");
    	beginTransaction();
    	//部门
    	StringBuffer sb = new StringBuffer();
    	sb.append("SELECT distinct t.dm, t.mc,'' fdm,'0' type         ");
    	sb.append("  FROM usr_gxsj.v_zxbz_dw_t t    ");
    	sb.append(" where t.sfzy = 0                ");
    	//班级2级节点
    	sb.append("union                            ");
    	sb.append("SELECT distinct yxdm || 'STU' dm, yxmc mc,'' fdm,'1' type   ");
    	sb.append("  FROM usr_gxsj.v_jwgl_bjzyyx    ");
    	//班号3级节点
    	sb.append("union                            ");
    	sb.append("SELECT distinct bh dm, bjmc mc,yxdm || 'STU' fdm,'1' type   ");
    	sb.append("  FROM usr_gxsj.v_jwgl_bjzyyx    ");
		List list = getJdbcTemplateSLMiddle().queryForList(sb.toString());
		for(Object obj : list){
			Map m = (Map)obj;
			String ogrgid = (String) m.get("dm");
			String orgname = (String) m.get("mc");
			String parentorgid = (String) m.get("fdm");
			String type = (String) m.get("type");
//			他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_orginfo where orgCode='"+ogrgid+"'",Integer.class);
			if(i>0)
				continue;
			TSysOrginfo tSysOrginfo = new TSysOrginfo();
			//logo
			int number = new Random().nextInt(20) + 1;
			URL url = this.getClass().getClassLoader().getResource("/");
			String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
			String sourceimgName = "defaultOrgLogo.png";
			String Source = pathRoot + "/upload/orgImg/" + sourceimgName;
			String targetimgName = ogrgid + ".png";
			String pathBody = "/upload/orgImg/" + targetimgName;
			String target = pathRoot + pathBody;
			FileUtil.copyFile(new File(Source), new File(target));
			tSysOrginfo.setOrgLogo(pathBody);
			
			tSysOrginfo.setIsAvailable(Constants.IS_AVAILABLE);
			tSysOrginfo.setOrgCode(ogrgid);
			tSysOrginfo.setOrgName(orgname);
			//班级
			if(StringUtils.equals(type, "1")){
				if(StringUtils.isEmpty(parentorgid))
					tSysOrginfo.setParentOrgCode("000001");//都放到学生这个2级节点下
				else
					tSysOrginfo.setParentOrgCode(parentorgid);
			}
			//部门	
			else{
				if(StringUtils.isEmpty(parentorgid))
					tSysOrginfo.setParentOrgCode("000000");
				else
					tSysOrginfo.setParentOrgCode(parentorgid);
			}
			tSysOrginfo.setOperatorName("system");
			tSysOrginfo.setCreateTime(new Date());
			tSysOrginfo.flushUpdateTime();
			tSysOrginfo.setOrgType(type);
			session.save(tSysOrginfo);
			
			//给每个部门单独的一般用户角色
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			if (rolelList == null || rolelList.size()==0) {
				TSysRoleinfo normalRole=new TSysRoleinfo();
				normalRole.setRoleId(UUID.randomUUID().toString());
				normalRole.setRoleName(orgname+"一般用户");
				normalRole.setDescription(orgname+"一般用户");
				normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				normalRole.setOperatorName("system");
				normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				normalRole.setDefaultFlg("1");//默认角色
				session.save(normalRole);
				
				TSysOrgroles orgrole=new TSysOrgroles();
				orgrole.setOrgRoleId(UUID.randomUUID().toString());
				orgrole.setOrgCode(ogrgid);
				orgrole.setRoleId(normalRole.getRoleId());
				orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				orgrole.setOperatorName("system");
				orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				session.save(orgrole);
			}
		}
		
		//给顶级机构单独的一般用户角色
		List<Map<String, Object>> rolelList0=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='000000' and r.defaultFlg='1'");
		if (rolelList0 == null || rolelList0.size()==0) {
			TSysRoleinfo normalRole=new TSysRoleinfo();
			normalRole.setRoleId(UUID.randomUUID().toString());
			normalRole.setRoleName(p.getAppProperties("projectName")+"一般用户");
			normalRole.setDescription(p.getAppProperties("projectName")+"一般用户");
			normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			normalRole.setOperatorName("system");
			normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			normalRole.setDefaultFlg("1");
			session.save(normalRole);
			
			TSysOrgroles orgrole=new TSysOrgroles();
			orgrole.setOrgRoleId(UUID.randomUUID().toString());
			orgrole.setOrgCode("000000");
			orgrole.setRoleId(normalRole.getRoleId());
			orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			orgrole.setOperatorName("system");
			orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			session.save(orgrole);
		}
		
		endTransaction(true);
		 
		beginTransaction();
		//人员
		list = getJdbcTemplateSLMiddle().queryForList("select xh as \"userid\",xm as \"username\",bh as \"orgcode\",bjmc as \"orgname\",zjh as \"identificationNo\",xh || '@stu.zjweu.edu.cn' as \"email\",decode(t.xbdm,2,0,t.xbdm) as \"sex\",'1' as \"type\" from usr_gxsj.v_bzks_ykt t where sfzx=1 " +
				//测试
//				" and xh='2013b03015' " +
				"");
		List list1 = getJdbcTemplateSLMiddle().queryForList("SELECT zgh as \"userid\",xm as \"username\",dm as \"orgcode\",mc as \"orgname\",zjh as \"identificationNo\",dzxx || '@zjweu.edu.cn' as \"email\",decode(t.xbdm,2,0,t.xbdm) as \"sex\",'0' as \"type\" FROM usr_gxsj.v_jzg_ky t where t.sfzx=1 " +
				//测试
//				" and zgh='00082' " +
				"");
		list.addAll(list1);
		//测试
//		getJdbcDao().getJdbcTemplate().execute("delete from tc_sys_userinfo where userAccount != 'admin' and userAccount='19841011'");
		int i1 = 0;
		for(Object obj : list){
			Map m = (Map)obj;
			String userid = (String) m.get("userid");
			String ogrgid = (String) m.get("orgcode");
			ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
			final String orgname = (String) m.get("orgname");
			final String username = (String) m.get("username");
			System.out.println(++i1+" "+username);
			BigDecimal sex = (BigDecimal) m.get("sex");
			String identificationNo = (String) m.get("identificationNo");
			String email = (String) m.get("email");
			String type = (String) m.get("type");
//			byte[] zp = (byte[]) m.get("ZP");
//			他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_userinfo where userId='"+userid+"'",Integer.class);
			if(i>0)
				continue;
			TSysUserinfo tSysUserinfo = new TSysUserinfo();
			tSysUserinfo.setEmail(email);
			tSysUserinfo.setUserId(userid);
			tSysUserinfo.setGender(sex==null?null:sex.toString());
			tSysUserinfo.setIsAvailable("1");
			// 设置初始密码  abcd1234
			tSysUserinfo.setUserPassword("2C608642CF08B54EDE1EEFA69941E4D4");
			tSysUserinfo.flushUpdateTime();
			tSysUserinfo.setUserName(username);
			// 设置默认头像
			tSysUserinfo.setPhotoUrl(registService.genDefaultHeadImg(tSysUserinfo.getUserId()));
//			if(zp!=null){
//				URL url = this.getClass().getClassLoader().getResource("/");
//				String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
//				FileUtil.byte2File(zp, pathRoot+"/upload/headImg/", userid + ".jpg");
//			}
			
			tSysUserinfo.setCreateTime(new Date());
			tSysUserinfo.setIdentificationNo(identificationNo);
			tSysUserinfo.setIdentificationTypeCode("0");
			tSysUserinfo.setOperatorName("system");
			session.save(tSysUserinfo);
			
			// 保存用户角色信息
				//查找用户所在部门的一般用户的roleId
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			String roleId=(String) rolelList.get(0).get("ROLEID");
				//先清
			getDataBaseDao().getJdbcTemplate().execute("delete from t_sys_userroles where userid!='admin' and userid='"+userid+"' ");
			TSysUserroles userRoles = new TSysUserroles();
			userRoles.setRoleId(roleId);
			userRoles.setUserId(tSysUserinfo.getUserId());
			userRoles.setOperatorAccount("admin");
			userRoles.setOperatorName("system");
			userRoles.setUserRoleId(UUID.randomUUID().toString());
			userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			userRoles.flushUpdateTime();
			session.save(userRoles);
			//加学生或老师角色
			userRoles = new TSysUserroles();
			if(StringUtils.equals(type, "1"))
				userRoles.setRoleId(Constants.SYS_STUDENT);
			else
				userRoles.setRoleId(Constants.SYS_STUFF);
			userRoles.setUserId(tSysUserinfo.getUserId());
			userRoles.setOperatorAccount("admin");
			userRoles.setOperatorName("system");
			userRoles.setUserRoleId(UUID.randomUUID().toString());
			userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			userRoles.flushUpdateTime();
			session.save(userRoles);
//			getDataBaseDao().getHibernateTemplate().save(userRoles);
		}
		endTransaction(true);
		System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户结束oracle,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
	}
    
    /**
     * Function Name               saveSyncUserSLSDInfo                                   
     * @throws URISyntaxException
     * @throws IOException
     * @throws SQLException          		             
     * @throws ErrorSqlException 
     * @description kettle过来的orginfo和userinfo再生成orgroles,userroles,roleinfo,环信,照片
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-5-31 上午10:44:01           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused", "unchecked" })
    private void genSomethingByKettle() throws URISyntaxException, IOException, SQLException, ErrorSqlException {
        Long start = System.currentTimeMillis();
        logger.info(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************开始处理kettle过来的orginfo和userinfo再生成orgroles,userroles,roleinfo,环信,照片");
        //System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************开始处理kettle过来的orginfo和userinfo再生成orgroles,userroles,roleinfo,环信,照片");
        beginTransaction();
        //部门
        List list = getDataBaseDao().queryForList("quartz.t_sys_orginfo.allData");
        for(Object obj : list){
            Map m = (Map)obj;
            String ogrgid = (String) m.get("ORGCODE");
            String orgname = (String) m.get("ORGNAME");
            String parentorgid = (String) m.get("PARENTORGCODE");
            String type = (String) m.get("ORGTYPE");
            
            //给每个部门单独的一般用户角色
            List params = new ArrayList();
            params.add(ogrgid);
            List<Map<String, Object>> rolelList=getDataBaseDao().queryForList("quartz.t_sys_orgroles.existsOrgcode",params);
            if (rolelList == null || rolelList.size()==0) {
                TSysRoleinfo normalRole=new TSysRoleinfo();
                normalRole.setRoleId(UUID.randomUUID().toString());
                normalRole.setRoleName(orgname+"一般用户");
                normalRole.setDescription(orgname+"一般用户");
                normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
                normalRole.setOperatorName("system");
                normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
                normalRole.setDefaultFlg("1");//默认角色
                session.save(normalRole);
                
                TSysOrgroles orgrole=new TSysOrgroles();
                orgrole.setOrgRoleId(UUID.randomUUID().toString());
                orgrole.setOrgCode(ogrgid);
                orgrole.setRoleId(normalRole.getRoleId());
                orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
                orgrole.setOperatorName("system");
                orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
                session.save(orgrole);
            }
        }
        
        //给顶级机构单独的一般用户角色
        List<Map<String, Object>> rolelList0=getDataBaseDao().queryForList("quartz.t_sys_orgroles.existsRootOrgcode");
        if (rolelList0 == null || rolelList0.size()==0) {
            TSysRoleinfo normalRole=new TSysRoleinfo();
            normalRole.setRoleId(UUID.randomUUID().toString());
            normalRole.setRoleName(p.getAppProperties("projectName")+"一般用户");
            normalRole.setDescription(p.getAppProperties("projectName")+"一般用户");
            normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
            normalRole.setOperatorName("system");
            normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
            normalRole.setDefaultFlg("1");
            session.save(normalRole);
            
            TSysOrgroles orgrole=new TSysOrgroles();
            orgrole.setOrgRoleId(UUID.randomUUID().toString());
            orgrole.setOrgCode("000000");
            orgrole.setRoleId(normalRole.getRoleId());
            orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
            orgrole.setOperatorName("system");
            orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
            session.save(orgrole);
        }
        endTransaction(true);
        beginTransaction();
        //人员
        list = getDataBaseDao().queryForList("quartz.t_sys_userinfo.allData");
        int i1 = 0;
        int x = 0;
        for(Object obj : list){
            Map m = (Map)obj;
            final String userid = (String) m.get("USERID");
            String ogrgid = (String) m.get("ORGCODE");
            if (StringUtils.isNotBlank(ogrgid)) {
                ogrgid = ogrgid.replace(" ", "");
            }
            ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
            final String username = (String) m.get("USERNAME");
//          final String orgname = (String) m.get("ORGNAME");
            String sex = (String) m.get("SEX");
            String identificationNo = (String) m.get("IDENTIFICATIONNO");
            String email = (String) m.get("EMAIL");
            String type = (String) m.get("TYPE");
            
//          Thread t=new Thread(){
//              public void run(){
//                  //注册环信IM
//                  registService.registIMUser(userid, username + "_" + orgname);
//              }
//          };
//          t.start();
            
            // 保存用户角色信息
                //判断该用户是否存在userroles
            List params = new ArrayList();
            params.add(userid);
            Integer count=getDataBaseDao().queryForInt("quartz.t_sys_userRoles.existsUserroles",params);
            if (0 == count) {
                //查找用户所在部门的一般用户的roleId
                params = new ArrayList();
                params.add(ogrgid);
                System.out.println(ogrgid);
                List<Map<String, Object>> rolelList=getDataBaseDao().queryForList("quartz.t_sys_orgroles.existsOrgcode",params);
                //用户表的orgcode在部门表里没有,则指向000000
                if(rolelList == null || rolelList.size()==0){
                    params = new ArrayList();
                    params.add("000000");
                    rolelList=getDataBaseDao().queryForList("quartz.t_sys_orgroles.existsOrgcode",params);
                }
                String roleId=(String) rolelList.get(0).get("ROLEID");
                TSysUserroles userRoles = new TSysUserroles();
                userRoles.setUserRoleId(UUID.randomUUID().toString());
                userRoles.setRoleId(roleId);
                userRoles.setUserId(userid);
                userRoles.setOperatorAccount("admin");
                userRoles.setOperatorName("system");
                userRoles.setUserRoleId(UUID.randomUUID().toString());
                userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
                userRoles.flushUpdateTime();
                session.save(userRoles);
                //加学生或老师角色
                userRoles = new TSysUserroles();
                userRoles.setUserRoleId(UUID.randomUUID().toString());
                if(StringUtils.equals(type, "1"))
                    userRoles.setRoleId(Constants.SYS_STUDENT);
                else
                    userRoles.setRoleId(Constants.SYS_STUFF);
                userRoles.setUserId(userid);
                userRoles.setOperatorAccount("admin");
                userRoles.setOperatorName("system");
                userRoles.setUserRoleId(UUID.randomUUID().toString());
                userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
                userRoles.flushUpdateTime();
                session.save(userRoles);
            }
        }
        endTransaction(true);
        beginTransaction();
        //生成部门图片
        List<TSysOrginfo> listTSysOrginfo = (List<TSysOrginfo>) dataBaseDao.getHibernateTemplate().find(" from TSysOrginfo t where t.orgLogo is null or t.orgLogo='' ");
        for(TSysOrginfo tSysOrginfo : listTSysOrginfo){
            URL url = this.getClass().getClassLoader().getResource("/");
            String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
            String sourceimgName = "defaultOrgLogo.png";
            String Source = pathRoot + "/upload/orgImg/" + sourceimgName;
            String targetimgName = tSysOrginfo.getOrgCode() + ".png";
            String pathBody = "/upload/orgImg/" + targetimgName;
            String target = pathRoot + pathBody;
            FileUtil.copyFile(new File(Source), new File(target));
            tSysOrginfo.setOrgLogo(pathBody);
            session.update(tSysOrginfo);
        }
        endTransaction(true);
        
        beginTransaction();
        //生成人员照片
        List<TSysUserinfo> listTSysUserinfo = (List<TSysUserinfo>) dataBaseDao.getHibernateTemplate().find(" from TSysUserinfo t where t.photoUrl is null or t.photoUrl='' ");
        for(TSysUserinfo tSysUserinfo : listTSysUserinfo){
            // 设置默认头像
            tSysUserinfo.setPhotoUrl(registService.genDefaultHeadImg(tSysUserinfo.getUserId()));
            session.update(tSysUserinfo);
        }
        endTransaction(true);
        
        logger.info(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************结束处理处理kettle过来的orginfo和userinfo再生成orgroles,userroles,roleinfo,环信,照片,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
        //System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************结束处理处理kettle过来的orginfo和userinfo再生成orgroles,userroles,roleinfo,环信,照片,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
    }

    
    
    /**
     * Function Name               saveSyncUserOrgCommon                                   
     * @throws URISyntaxException
     * @throws IOException          		             
     * @throws SQLException 
     * @description 同步基础数据,中间库到本地库,标准
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-19 下午5:40:28           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused" })
	private void saveSyncUserOrgCommon() throws URISyntaxException, IOException, SQLException {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户开始oracle");
    	beginTransaction();
    	//部门
    	StringBuffer sb = new StringBuffer();
    	sb.append("SELECT * ");
    	sb.append("  FROM v_sys_orginfo ");
		List list = getJdbcTemplateSLMiddle().queryForList(sb.toString());
		for(Object obj : list){
			Map m = (Map)obj;
			String ogrgid = (String) m.get("orgCode");
			String orgname = (String) m.get("orgName");
			String parentorgid = (String) m.get("parentOrgCode");
			String telephone = (String) m.get("telephone");
			String address = (String) m.get("address");
			String type = (String) m.get("type");
			// 他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_orginfo where orgCode='"+ogrgid+"'",Integer.class);
			if(i>0)
				continue;
			TSysOrginfo tSysOrginfo = new TSysOrginfo();
			//logo
			URL url = this.getClass().getClassLoader().getResource("/");
			String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
			String sourceimgName = "defaultOrgLogo.png";
			String Source = pathRoot + "/upload/orgImg/" + sourceimgName;
			String targetimgName = ogrgid + ".png";
			String pathBody = "/upload/orgImg/" + targetimgName;
			String target = pathRoot + pathBody;
			FileUtil.copyFile(new File(Source), new File(target));
			tSysOrginfo.setOrgLogo(pathBody);
			
			tSysOrginfo.setIsAvailable(Constants.IS_AVAILABLE);
			tSysOrginfo.setOrgCode(ogrgid);
			tSysOrginfo.setOrgName(orgname);
			tSysOrginfo.setParentOrgCode(parentorgid);
			tSysOrginfo.setTelephone(telephone);
			tSysOrginfo.setAddress(address);
			tSysOrginfo.setOperatorName("system");
			tSysOrginfo.setCreateTime(new Date());
			tSysOrginfo.flushUpdateTime();
			tSysOrginfo.setOrgType(type);
			session.save(tSysOrginfo);
			
			//给每个部门单独的一般用户角色
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			if (rolelList == null || rolelList.size()==0) {
				TSysRoleinfo normalRole=new TSysRoleinfo();
				normalRole.setRoleId(UUID.randomUUID().toString());
				normalRole.setRoleName(orgname+"一般用户");
				normalRole.setDescription(orgname+"一般用户");
				normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				normalRole.setOperatorName("system");
				normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				normalRole.setDefaultFlg("1");//默认角色
				session.save(normalRole);
				
				TSysOrgroles orgrole=new TSysOrgroles();
				orgrole.setOrgRoleId(UUID.randomUUID().toString());
				orgrole.setOrgCode(ogrgid);
				orgrole.setRoleId(normalRole.getRoleId());
				orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				orgrole.setOperatorName("system");
				orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				session.save(orgrole);
			}
		}
		
		//给顶级机构单独的一般用户角色
		List<Map<String, Object>> rolelList0=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='000000' and r.defaultFlg='1'");
		if (rolelList0 == null || rolelList0.size()==0) {
			TSysRoleinfo normalRole=new TSysRoleinfo();
			normalRole.setRoleId(UUID.randomUUID().toString());
			normalRole.setRoleName(p.getAppProperties("projectName")+"一般用户");
			normalRole.setDescription(p.getAppProperties("projectName")+"一般用户");
			normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			normalRole.setOperatorName("system");
			normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			normalRole.setDefaultFlg("1");
			session.save(normalRole);
			
			TSysOrgroles orgrole=new TSysOrgroles();
			orgrole.setOrgRoleId(UUID.randomUUID().toString());
			orgrole.setOrgCode("000000");
			orgrole.setRoleId(normalRole.getRoleId());
			orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			orgrole.setOperatorName("system");
			orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			session.save(orgrole);
		}
		
		endTransaction(true);
		 
		beginTransaction();
		//人员
		list = getJdbcTemplateSLMiddle().queryForList("SELECT * FROM v_sys_userinfo ");
		int i1 = 0;
		for(Object obj : list){
			Map m = (Map)obj;
			String userid = (String) m.get("userId");
			String ogrgid = (String) m.get("orgCode");
			ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
			final String username = (String) m.get("userName");
			System.out.println(++i1+" "+username);
			String gender = (String) m.get("gender");
			// 性别为空时，默认值为"1"男
			if (StringUtils.isEmpty(gender)) {
				gender = "1";
			}
			String age = (String) m.get("age");
			String identificationNo = (String) m.get("identificationNo");
			String jobPosition = (String) m.get("jobPosition");
			String professionalRanks = (String) m.get("professionalRanks");
			String officeTel = (String) m.get("officeTel");
			String mobile = (String) m.get("mobile");
			String email = (String) m.get("email");
			// 如果邮箱为空。生成默认邮箱，确保不重复
			if (StringUtils.isEmpty(email)) {
				email = userid + "@qqq.com";
			}
			String type = (String) m.get("type");
			// 他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_userinfo where userId='"+userid+"'",Integer.class);
			if(i>0)
				continue;
			TSysUserinfo tSysUserinfo = new TSysUserinfo();
			tSysUserinfo.setEmail(email);
			tSysUserinfo.setUserId(userid);
			tSysUserinfo.setUserName(username);
			tSysUserinfo.setGender(gender);
			tSysUserinfo.setAge(age==null?null:Integer.parseInt(age));
			tSysUserinfo.setIdentificationNo(identificationNo);
			tSysUserinfo.setIdentificationTypeCode("0");
			tSysUserinfo.setJobPosition(jobPosition);
			tSysUserinfo.setProfessionalRanks(professionalRanks);
			tSysUserinfo.setOfficeTel(officeTel);
			tSysUserinfo.setMobile(mobile);
			
			tSysUserinfo.setIsAvailable("1");
			// 设置初始密码  abcd1234
			tSysUserinfo.setUserPassword("2C608642CF08B54EDE1EEFA69941E4D4");
			tSysUserinfo.flushUpdateTime();
			// 设置默认头像
			tSysUserinfo.setPhotoUrl(registService.genDefaultHeadImg(tSysUserinfo.getUserId()));
			
			tSysUserinfo.setCreateTime(new Date());
			tSysUserinfo.setOperatorName("system");
			session.save(tSysUserinfo);
			
			// 保存用户角色信息
				//查找用户所在部门的一般用户的roleId
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			String roleId=(String) rolelList.get(0).get("ROLEID");
				//先清
			getDataBaseDao().getJdbcTemplate().execute("delete from t_sys_userroles where userid!='admin' and userid='"+userid+"' ");
			TSysUserroles userRoles = new TSysUserroles();
			userRoles.setRoleId(roleId);
			userRoles.setUserId(tSysUserinfo.getUserId());
			userRoles.setOperatorAccount("admin");
			userRoles.setOperatorName("system");
			userRoles.setUserRoleId(UUID.randomUUID().toString());
			userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			userRoles.flushUpdateTime();
			session.save(userRoles);
			//加学生或老师角色
			userRoles = new TSysUserroles();
			if(StringUtils.equals(type, "1"))
				userRoles.setRoleId(Constants.SYS_STUDENT);
			else
				userRoles.setRoleId(Constants.SYS_STUFF);
			userRoles.setUserId(tSysUserinfo.getUserId());
			userRoles.setOperatorAccount("admin");
			userRoles.setOperatorName("system");
			userRoles.setUserRoleId(UUID.randomUUID().toString());
			userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			userRoles.flushUpdateTime();
			session.save(userRoles);
//			getDataBaseDao().getHibernateTemplate().save(userRoles);
		}
		endTransaction(true);
		System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户结束oracle,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
	}
    
    
	
    /**
     * Function Name               saveSyncUserGSWInfo                                   
     * @throws URISyntaxException
     * @throws IOException          		             
     * @throws SQLException 
     * @throws ErrorSqlException 
     * @description 同步基础数据到工商外
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-19 下午5:40:28           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused" })
	private void saveSyncUserGSWInfo() throws URISyntaxException, IOException, SQLException, ErrorSqlException {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户开始oracle");
    	beginTransaction();
    	//部门
    	StringBuffer sb = new StringBuffer();
    	sb.append("select to_char(decode(o.jjdm,1,'000000',o.jjdm)) as \"dm\",o.jgmc as \"mc\",to_char(decode(o.fjgdm,1,'000000',o.fjgdm)) as \"fdm\"  from v_orginfo o ");
		List list = getDataBaseDao().queryNoKeyForList(sb.toString(),null);
		for(Object obj : list){
			Map m = (Map)obj;
			String ogrgid = (String) m.get("dm");
			ogrgid = StringUtils.equals(ogrgid, "1")?"000000":ogrgid;
			String orgname = (String) m.get("mc");
			String parentorgid = (String) m.get("fdm");
//			他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_orginfo where orgCode='"+ogrgid+"'",Integer.class);
			if(i>0)
				continue;
			TSysOrginfo tSysOrginfo = new TSysOrginfo();
			//logo
			int number = new Random().nextInt(20) + 1;
			URL url = this.getClass().getClassLoader().getResource("/");
			String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
			String sourceimgName = "defaultOrgLogo.png";
			String Source = pathRoot + "/upload/orgImg/" + sourceimgName;
			String targetimgName = ogrgid + ".png";
			String pathBody = "/upload/orgImg/" + targetimgName;
			String target = pathRoot + pathBody;
			FileUtil.copyFile(new File(Source), new File(target));
			tSysOrginfo.setOrgLogo(pathBody);
			
			tSysOrginfo.setIsAvailable(Constants.IS_AVAILABLE);
			tSysOrginfo.setOrgCode(ogrgid);
			tSysOrginfo.setOrgName(orgname);
			if(StringUtils.isEmpty(parentorgid))
				tSysOrginfo.setParentOrgCode("000000");
			else
				tSysOrginfo.setParentOrgCode(parentorgid);
			tSysOrginfo.setOperatorName("system");
			tSysOrginfo.setCreateTime(new Date());
			tSysOrginfo.flushUpdateTime();
			session.save(tSysOrginfo);
//			getDataBaseDao().getHibernateTemplate().save(tSysOrginfo);
			
			//给每个部门单独的一般用户角色
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			if (rolelList == null || rolelList.size()==0) {
				TSysRoleinfo normalRole=new TSysRoleinfo();
				normalRole.setRoleId(UUID.randomUUID().toString());
				normalRole.setRoleName(orgname+"一般用户");
				normalRole.setDescription(orgname+"一般用户");
				normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				normalRole.setOperatorName("system");
				normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				normalRole.setDefaultFlg("1");//默认角色
				session.save(normalRole);
				
				TSysOrgroles orgrole=new TSysOrgroles();
				orgrole.setOrgRoleId(UUID.randomUUID().toString());
				orgrole.setOrgCode(ogrgid);
				orgrole.setRoleId(normalRole.getRoleId());
				orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
				orgrole.setOperatorName("system");
				orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
				session.save(orgrole);
			}
		}
		
		//给顶级机构单独的一般用户角色
		List<Map<String, Object>> rolelList0=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='000000' and r.defaultFlg='1'");
		if (rolelList0 == null || rolelList0.size()==0) {
			TSysRoleinfo normalRole=new TSysRoleinfo();
			normalRole.setRoleId(UUID.randomUUID().toString());
			normalRole.setRoleName(p.getAppProperties("projectName")+"一般用户");
			normalRole.setDescription(p.getAppProperties("projectName")+"一般用户");
			normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			normalRole.setOperatorName("system");
			normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			normalRole.setDefaultFlg("1");
			session.save(normalRole);
			
			TSysOrgroles orgrole=new TSysOrgroles();
			orgrole.setOrgRoleId(UUID.randomUUID().toString());
			orgrole.setOrgCode("000000");
			orgrole.setRoleId(normalRole.getRoleId());
			orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
			orgrole.setOperatorName("system");
			orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			session.save(orgrole);
		}
		
		endTransaction(true);
		 
		beginTransaction();
		//人员
		list = getDataBaseDao().queryNoKeyForList("select to_char(u.yhid) as \"userid\",u.yhxm as \"username\",to_char(decode(u.bmid,1,'000000',u.bmid)) as \"orgcode\",decode(u.xb,'女','0','男','1','') as \"sex\",u.sfzjh as \"identificationNo\",nvl(u.YX,u.yhid || '@sicfl.edu.cn') as \"email\",'' as \"mobile\",u.type as \"type\" from v_userinfo u where u.yhxm !='sicfltest' " +
				//测试
//				" and xh='2013b03015' " +
				"",null);
		//测试
//		getJdbcDao().getJdbcTemplate().execute("delete from tc_sys_userinfo where userAccount != 'admin' and userAccount='19841011'");
		int i1 = 0;
		for(Object obj : list){
			Map m = (Map)obj;
			String userid = (String) m.get("userid");
			String ogrgid = (String) m.get("orgcode");
			ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
			final String username = (String) m.get("username");
			System.out.println(++i1+" "+username);
			String sex = (String) m.get("sex");
			String identificationNo = (String) m.get("identificationNo");
			String email = (String) m.get("email");
			String mobile = (String) m.get("mobile");
			String type = (String) m.get("type");
//			byte[] zp = (byte[]) m.get("ZP");
//			他有我有,跳过
			int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_userinfo where userId='"+userid+"'",Integer.class);
			if(i>0)
				continue;
			TSysUserinfo tSysUserinfo = new TSysUserinfo();
			tSysUserinfo.setType(type);
			tSysUserinfo.setEmail(email);
			tSysUserinfo.setTelephone(mobile);
			tSysUserinfo.setMobile(mobile);
			tSysUserinfo.setUserId(userid);
			tSysUserinfo.setGender(sex);
			tSysUserinfo.setIsAvailable("1");
			// 设置初始密码  abcd1234
			tSysUserinfo.setUserPassword("2C608642CF08B54EDE1EEFA69941E4D4");
			tSysUserinfo.flushUpdateTime();
			tSysUserinfo.setUserName(username);
			// 设置默认头像
			tSysUserinfo.setPhotoUrl(registService.genDefaultHeadImg(tSysUserinfo.getUserId()));
//			if(zp!=null){
//				URL url = this.getClass().getClassLoader().getResource("/");
//				String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
//				FileUtil.byte2File(zp, pathRoot+"/upload/headImg/", userid + ".jpg");
//			}
			
			tSysUserinfo.setCreateTime(new Date());
			tSysUserinfo.setIdentificationNo(identificationNo);
			tSysUserinfo.setIdentificationTypeCode("0");
			tSysUserinfo.setOperatorName("system");
			session.save(tSysUserinfo);
			
			// 保存用户角色信息
				//查找用户所在部门的一般用户的roleId
			List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
			String roleId=(String) rolelList.get(0).get("ROLEID");
				//先清
			getDataBaseDao().getJdbcTemplate().execute("delete from t_sys_userroles where userid!='admin' and userid='"+userid+"' ");
			TSysUserroles userRoles = new TSysUserroles();
			userRoles.setRoleId(roleId);
			userRoles.setUserId(tSysUserinfo.getUserId());
			userRoles.setOperatorAccount("admin");
			userRoles.setOperatorName("system");
			userRoles.setUserRoleId(UUID.randomUUID().toString());
			userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
			userRoles.flushUpdateTime();
			session.save(userRoles);
//			getDataBaseDao().getHibernateTemplate().save(userRoles);
		}
		endTransaction(true);
		System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户结束oracle,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
	}
    
    /**
     * Function Name               saveSyncUserInfo                                   
     * @throws URISyntaxException
     * @throws IOException          		             
     * @description 同步基础数据到中医药
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-19 下午5:40:28           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused" })
    private void saveSyncUserInfo() throws URISyntaxException, IOException {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户开始oracle");
    	beginTransaction();
    	//部门
    	List list;
    	list = getDataBaseDao().getJdbcTemplate().queryForList("select t.dwh ogrgid,t.dwmc orgname from usr_mapp.YXSDWJBSJZL t  ");
    	//先清
//		if(list!=null && list.size()>0)
//			getJdbcTemplate().execute("delete from t_sys_orginfo where orgCode !='000000'");
    	//测试
//		getJdbcDao().getJdbcTemplate().execute("delete from tc_sys_orginfo where orgCode !='000000' and orgCode = '1420000'");
    	for(Object obj : list){
    		Map m = (Map)obj;
    		String ogrgid = (String) m.get("ogrgid");
    		String orgname = (String) m.get("orgname");
//			他有我有,跳过
    		int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_orginfo where orgCode='"+ogrgid+"'",Integer.class);
    		if(i>0)
    			continue;
    		TSysOrginfo tSysOrginfo = new TSysOrginfo();
    		//logo
    		int number = new Random().nextInt(20) + 1;
    		URL url = this.getClass().getClassLoader().getResource("/");
    		String pathRoot = url.getPath().replace("/WEB-INF/classes", ""); // 真实路径
    		String sourceimgName = "defaultOrgLogo.png";
    		String Source = pathRoot + "/upload/orgImg/" + sourceimgName;
    		String targetimgName = ogrgid + ".png";
    		String pathBody = "/upload/orgImg/" + targetimgName;
    		String target = pathRoot + pathBody;
    		FileUtil.copyFile(new File(Source), new File(target));
    		tSysOrginfo.setOrgLogo(pathBody);
    		
    		tSysOrginfo.setIsAvailable(Constants.IS_AVAILABLE);
    		tSysOrginfo.setOrgCode(ogrgid);
    		tSysOrginfo.setOrgName(orgname);
    		tSysOrginfo.setParentOrgCode("000000");
    		tSysOrginfo.setOperatorName("system");
    		tSysOrginfo.setCreateTime(new Date());
    		tSysOrginfo.flushUpdateTime();
    		session.save(tSysOrginfo);
//			getDataBaseDao().getHibernateTemplate().save(tSysOrginfo);
    		
    		
    		
    		//给每个部门单独的一般用户角色
    		List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
    		if (rolelList == null || rolelList.size()==0) {
    			TSysRoleinfo normalRole=new TSysRoleinfo();
    			normalRole.setRoleId(UUID.randomUUID().toString());
    			normalRole.setRoleName(orgname+"一般用户");
    			normalRole.setDescription(orgname+"一般用户");
    			normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
    			normalRole.setOperatorName("system");
    			normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
    			normalRole.setDefaultFlg("1");//默认角色
    			session.save(normalRole);
    			
    			TSysOrgroles orgrole=new TSysOrgroles();
    			orgrole.setOrgRoleId(UUID.randomUUID().toString());
    			orgrole.setOrgCode(ogrgid);
    			orgrole.setRoleId(normalRole.getRoleId());
    			orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
    			orgrole.setOperatorName("system");
    			orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
    			session.save(orgrole);
    		}
    	}
    	
    	//给顶级机构单独的一般用户角色
    	List<Map<String, Object>> rolelList0=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='000000' and r.defaultFlg='1'");
    	if (rolelList0 == null || rolelList0.size()==0) {
    		TSysRoleinfo normalRole=new TSysRoleinfo();
    		normalRole.setRoleId(UUID.randomUUID().toString());
    		normalRole.setRoleName(p.getAppProperties("projectName")+"一般用户");
    		normalRole.setDescription(p.getAppProperties("projectName")+"一般用户");
    		normalRole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
    		normalRole.setOperatorName("system");
    		normalRole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
    		normalRole.setDefaultFlg("1");
    		session.save(normalRole);
    		
    		TSysOrgroles orgrole=new TSysOrgroles();
    		orgrole.setOrgRoleId(UUID.randomUUID().toString());
    		orgrole.setOrgCode("000000");
    		orgrole.setRoleId(normalRole.getRoleId());
    		orgrole.setUpdateTime(new Timestamp(System.currentTimeMillis()));
    		orgrole.setOperatorName("system");
    		orgrole.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
    		session.save(orgrole);
    	}
    	
    	endTransaction(true);
    	
    	beginTransaction();
    	//人员
    	list = getDataBaseDao().getJdbcTemplate().queryForList("select distinct * from usr_mapp.v_userinfo1 k ");
//		if(list!=null && list.size()>0)
//			getDataBaseDao().getJdbcTemplate().execute("delete from t_sys_userinfo where userId != 'admin'");
    	//测试
//		getJdbcDao().getJdbcTemplate().execute("delete from tc_sys_userinfo where userAccount != 'admin' and userAccount='19841011'");
    	for(Object obj : list){
    		Map m = (Map)obj;
    		final String useraccount = (String) m.get("useraccount");
    		String ogrgid = (String) m.get("ogrgid");
    		ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
    		final String orgname = (String) m.get("orgname");
    		final String username = (String) m.get("username");
    		String sex = (String) m.get("sex");
    		String sfzjh = (String) m.get("sfzjh");
//			他有我有,跳过
    		int i = getDataBaseDao().getJdbcTemplate().queryForObject("select count(1) from t_sys_userinfo where userId='"+useraccount+"'",Integer.class);
    		if(i>0)
    			continue;
    		TSysUserinfo tSysUserinfo = new TSysUserinfo();
    		tSysUserinfo.setUserId(useraccount);
    		if(StringUtils.isNotEmpty(sex)){
    			if(sex.equals("女")) tSysUserinfo.setGender("0");
    			else if(sex.equals("男")) tSysUserinfo.setGender("1");
    			else tSysUserinfo.setGender("2");
    		}
    		tSysUserinfo.setIsAvailable("1");
    		// 设置初始密码  abcd1234
    		tSysUserinfo.setUserPassword("2C608642CF08B54EDE1EEFA69941E4D4");
    		tSysUserinfo.flushUpdateTime();
    		tSysUserinfo.setUserName(username);
    		// 设置默认头像
    		tSysUserinfo.setPhotoUrl(registService.genDefaultHeadImg(tSysUserinfo.getUserId()));
    		tSysUserinfo.setCreateTime(new Date());
    		tSysUserinfo.setIdentificationNo(sfzjh);
    		tSysUserinfo.setIdentificationTypeCode("0");
    		tSysUserinfo.setOperatorName("system");
    		session.save(tSysUserinfo);
//			getDataBaseDao().getHibernateTemplate().save(tSysUserinfo);
    		Thread t=new Thread(){
    			public void run(){
    				//注册环信IM
    				registService.registIMUser(useraccount, username + "_" + orgname);
    			}
    		};
    		t.start();
    		
    		// 保存用户角色信息
    		//查找用户所在部门的一般用户的roleId
    		List<Map<String, Object>> rolelList=getDataBaseDao().getJdbcTemplate().queryForList("SELECT r.ROLEID from T_SYS_ORGROLES o LEFT JOIN T_SYS_ROLEINFO r on o.ROLEID=r.ROLEID where o.ORGCODE='"+ogrgid+"' and r.defaultFlg='1'");
    		String roleId=(String) rolelList.get(0).get("ROLEID");
    		//先清
    		getDataBaseDao().getJdbcTemplate().execute("delete from t_sys_userroles where userid!='admin' and userid='"+useraccount+"' ");
    		TSysUserroles userRoles = new TSysUserroles();
    		userRoles.setRoleId(roleId);
    		userRoles.setUserId(tSysUserinfo.getUserId());
    		userRoles.setOperatorAccount("admin");
    		userRoles.setOperatorName("system");
    		userRoles.setUserRoleId(UUID.randomUUID().toString());
    		userRoles.setIsAvailable(DBConstant.DB_STATUS_ABLCE);
    		userRoles.flushUpdateTime();
    		session.save(userRoles);
//			getDataBaseDao().getHibernateTemplate().save(userRoles);
    	}
    	endTransaction(true);
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步用户结束oracle,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
    }
    
    /**
     * Function Name               sendQyhGuanZhuEmail                                             		             
     * @throws Exception 
     * @description 发送企业号用户邀请关注的邮件
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-26 下午1:52:37           张旭星                     Initial
     **********************************************************************
     */
    private void sendQyhGuanZhuEmail() throws Exception {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************发送企业号用户邀请关注的邮件开始");
    	List<TSysEmailmq> list = (List<TSysEmailmq>) dataBaseDao.getHibernateTemplate().find("from TSysEmailmq where rownum<=450", null);
    	for(TSysEmailmq tSysEmailmq:list){
    		SendEmailCode.sendEmailWX(tSysEmailmq.getEmail());
    		dataBaseDao.delete(tSysEmailmq);
    	}
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************发送企业号用户邀请关注的邮件结束,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
	}
    
    /**
     * Function Name               saveSyncUserOrgRoleQy                                   
     * @throws Exception 
     * @description 同步本地库机构角色人员到企业号
     * Modify History:              Date             Programmer       Notes
     *                            ---------        ---------------  ---------
     *                      2016-4-19 下午5:39:59           张旭星                     Initial
     **********************************************************************
     */
    @SuppressWarnings({ "rawtypes", "unused", "unchecked" })
    private void saveSyncUserOrgRoleQy() throws Exception {
    	Long start = System.currentTimeMillis();
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步企业号开始");
    	
    	beginTransaction();
    	//部门  
    	List list = getDataBaseDao().getJdbcTemplate().queryForList("SELECT LEVEL,T.ORGNAME,T.ORGCODE,T.PARENTORGCODE,(SELECT T1.QYORGID FROM T_SYS_ORGINFO T1 WHERE T1.ORGCODE= T.PARENTORGCODE) QYPARENTID,(SELECT NVL(MAX(T2.QYORGID),"+p.getAppProperties("qiYeHaoBeginId")+") FROM T_SYS_ORGINFO T2) MAXQYORGID FROM T_SYS_ORGINFO T WHERE (T.QYORGID IS NULL OR T.QYORGID = '') " +
    			//测试
//    			" AND T.ORGCODE in ('1023','1025','003201401','000001','1009','120201201','131200301','1212') " + 
    			" AND T.ORGCODE!='"+p.getAppProperties("rootOrgCode")+"' START WITH T.ORGCODE='"+p.getAppProperties("rootOrgCode")+"' CONNECT BY PRIOR T.ORGCODE=T.PARENTORGCODE ORDER BY LEVEL ");
    	int i = 0;
    	Map qyParentIdMap = new HashMap();
    	for(Object obj : list){
    		Map m = (Map)obj;
    		String ORGCODE = (String) m.get("ORGCODE");
    		String parentOrgCode = (String) m.get("PARENTORGCODE");
    		String orgName = (String) m.get("ORGNAME");
    		if(orgName==null) orgName="";
    		System.out.println(orgName);
    		BigDecimal maxQyOrgId = (BigDecimal) m.get("MAXQYORGID");
    		BigDecimal QYPARENTID = (BigDecimal) m.get("QYPARENTID");
    		//如果父部门为空,则父id=18
    		final String qyParentId;
    		if (null == QYPARENTID) {
    			// LEVEL > 2时 	qyParentId 从缓存中取得
    			BigDecimal level = (BigDecimal) m.get("LEVEL");
        		String qyParentOrgId = null;
        		if (null != level && level.intValue() > 2) {
        			qyParentOrgId = (String) qyParentIdMap.get(parentOrgCode);
        		}
        		
        		if (!StringUtils.isEmpty(qyParentOrgId)) {
        			qyParentId = qyParentOrgId;
        		} else {
        			qyParentId = p.getAppProperties("qiYeHaoRootOrgId");
        		}
    		} else {
    			qyParentId = QYPARENTID.toString();
    		}
    		
    		final String orgId = (maxQyOrgId.intValue()+ i) + "";
    		i++;
    		String url;
			try {
//				url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createOrgQy&orgName="+URLEncoder.encode(orgName,"UTF-8")+"&parentId="+qyParentId+"&orgId="+orgId;
//				HTTPClientUtils.sendGet(url);
				String access_token = WeixinUtil.getAccessToken(new ParamesAPI().getCorpId(), new ParamesAPI().getSecret()).getToken();
				String postJson = MGroup.create(orgName,qyParentId,orgId);
				WeixinUtil.PostMessage(access_token, "POST", MGroup.CREATE_URL, postJson);
			} catch (Exception e) {
				e.printStackTrace();
			}
			TSysOrginfo tSysOrginfo = (TSysOrginfo) session.load(TSysOrginfo.class, ORGCODE);
			tSysOrginfo.setQyOrgId(Integer.parseInt(orgId));
			session.update(tSysOrginfo);
			qyParentIdMap.put(ORGCODE, orgId);
//    		Thread t=new Thread(){
//    			public void run(){
//    	    		String url;
//					try {
//						url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createOrgQy&orgName="+orgName+"&parentId="+qyParentId+"&orgId="+orgId;
//						HTTPClientUtils.sendGet(url);
//					} catch (FileNotFoundException e) {
//						e.printStackTrace();
//					} catch (IOException e) {
//						e.printStackTrace();
//					}
//    			}
//    		};
//    		t.start();
    	}
    	endTransaction(true);

    	beginTransaction();
    	//标签
    	list = getDataBaseDao().getJdbcTemplate().queryForList("SELECT R.ROLENAME,R.ROLEID,(SELECT NVL(MAX(T.QYTAGID),"+p.getAppProperties("qiYeHaoBeginId")+") FROM T_SYS_ROLEINFO T) MAXQYORGID FROM T_SYS_ROLEINFO R WHERE (R.QYTAGID IS NULL OR R.QYTAGID = '') AND R.ISAVAILABLE='1' AND R.DEFAULTFLG='0'");
    	i=0;
    	for(Object obj : list){
    		Map m = (Map)obj;
    		String ROLEID = (String) m.get("ROLEID");
    		String ROLENAME = (String) m.get("ROLENAME");
    		if(ROLENAME==null) ROLENAME="";
    		System.out.println(ROLENAME);
    		BigDecimal MAXQYORGID = (BigDecimal) m.get("MAXQYORGID");
    		final String tagId = (MAXQYORGID.intValue() + i) + "";
    		i++;
			String url;
			try {
//				url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createTagQy&tagName="+URLEncoder.encode(ROLENAME,"UTF-8")+"&tagId="+tagId;
//				HTTPClientUtils.sendGet(url);
				String access_token = WeixinUtil.getAccessToken(new ParamesAPI().getCorpId(), new ParamesAPI().getSecret()).getToken();
				String postData = MTag.create_tag(ROLENAME,tagId);
				WeixinUtil.PostMessage(access_token, "POST", MTag.CREATE_TAG_URL, postData);
			} catch (Exception e) {
				e.printStackTrace();
			}
			TSysRoleinfo tSysRoleinfo = (TSysRoleinfo) session.load(TSysRoleinfo.class, ROLEID);
			tSysRoleinfo.setQyTagId(Integer.parseInt(tagId));
			session.update(tSysRoleinfo);
//    		Thread t=new Thread(){
//    			public void run(){
//    				String url;
//    				try {
//    					url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createTagQy&tagName="+ROLENAME+"&tagId="+tagId;
//    					HTTPClientUtils.sendGet(url);
//    				} catch (FileNotFoundException e) {
//    					e.printStackTrace();
//    				} catch (IOException e) {
//    					e.printStackTrace();
//    				}
//    			}
//    		};
//    		t.start();
    	}
    	endTransaction(true);
    	
    	beginTransaction();
    	//人员
    	list = getDataBaseDao().getJdbcTemplate().queryForList("SELECT T.USERID,T.USERNAME,T.EMAIL,T.JOBPOSITION,T.GENDER,O.ORGNAME,O.QYORGID,R.QYTAGID FROM T_SYS_USERINFO T,T_SYS_ORGINFO   O,T_SYS_USERROLES UR,T_SYS_ORGROLES  ORS,T_SYS_ROLEINFO R WHERE R.ROLEID=UR.ROLEID " +
    			//测试
//    			" AND T.USERID in ('20081001','19831001') " +
    			" AND UR.USERID = T.USERID AND ORS.ORGCODE = O.ORGCODE AND ORS.ROLEID = UR.ROLEID AND O.ISAVAILABLE='1' AND T.ISAVAILABLE='1' AND UR.ISAVAILABLE='1' AND ORS.ISAVAILABLE='1' and t.syncqyflg is null ");
    	for(Object obj : list){
    		Map m = (Map)obj;
    		final String USERID = (String) m.get("USERID");
    		final String EMAIL = (String) m.get("EMAIL");
    		final String ORGNAME = (String) m.get("ORGNAME");
    		String USERNAME = (String) m.get("USERNAME");
    		if(USERNAME==null) USERNAME="";
    		System.out.println(USERNAME);
    		String JOBPOSITION = (String) m.get("JOBPOSITION");
    		if(JOBPOSITION==null) JOBPOSITION="";
    		BigDecimal QYORGID = (BigDecimal) m.get("QYORGID");
    		if(QYORGID==null)
    			QYORGID = new BigDecimal(p.getAppProperties("qiYeHaoRootOrgId"));
    		final BigDecimal QYTAGID = (BigDecimal) m.get("QYTAGID");
    		final String GENDER = (String) m.get("GENDER");
    		String url;
			try {
//				url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createUserQy&userId="+USERID+"&email="+EMAIL+"&userName="+URLEncoder.encode(USERNAME,"UTF-8")+"&jobPosition="+URLEncoder.encode(JOBPOSITION,"UTF-8")+"&orgId="+QYORGID+"&gender="+GENDER;
//				HTTPClientUtils.sendGet(url);
//				
				String access_token = WeixinUtil.getAccessToken(new ParamesAPI().getCorpId(), new ParamesAPI().getSecret()).getToken();
				String postJson = MPerson.create(USERID, USERNAME,"["+QYORGID+"]",JOBPOSITION, "", GENDER, "", EMAIL, "");
				WeixinUtil.PostMessage(access_token, "POST", MPerson.CREATE_URL, postJson);
				
				if(QYTAGID!=null){
//					url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?addTagUsers&tagId="+QYTAGID+"&userList="+USERID;
//					HTTPClientUtils.sendGet(url);
//					
					String userIds = StringUtil.concatStrForWechat(USERID);
					String postData = MTag.add_tag_person(QYTAGID.toString(),userIds);
					WeixinUtil.PostMessage(access_token, "POST", MTag.ADD_TAG_PERSON, postData);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			TSysUserinfo tSysUserinfo = (TSysUserinfo) session.load(TSysUserinfo.class, USERID);
			//已同步企业号标识
			tSysUserinfo.setSyncQyFlg("1");
			session.update(tSysUserinfo);
			//邮件邀请关注,syncqyflg=‘1’的用户不会进循环
//			TSysEmailmq tSysEmailmq = new TSysEmailmq();
//			tSysEmailmq.setEmail(tSysUserinfo.getEmail());
//			tSysEmailmq.setIsavailable("1");
//			tSysEmailmq.setMqid(UUID.randomUUID().toString());
//			tSysEmailmq.setOperatorname("system");
//			tSysEmailmq.setUpdatetime(new Timestamp(new Date().getTime()));
//			session.save(tSysEmailmq);
//    		Thread t=new Thread(){
//    			public void run(){
//    	    		String url;
//					try {
//						url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?createUserQy&userId="+USERID+"&email="+EMAIL+"&userName="+USERNAME+"&jobPosition="+JOBPOSITION+"&orgId="+QYORGID+"&gender="+GENDER;
//						HTTPClientUtils.sendGet(url);
//						if(QYTAGID!=null){
//							url = p.getAppProperties("weChatInterfaceUrl") + "/weixinCmsSiteController.do?addTagUsers&tagId="+QYTAGID+"&userList="+USERID;
//							HTTPClientUtils.sendGet(url);
//						}
//					} catch (FileNotFoundException e) {
//						e.printStackTrace();
//					} catch (IOException e) {
//						e.printStackTrace();
//					}
//    			}
//    		};
//    		t.start();
    	}
    	endTransaction(true);
    	System.out.println(DateUtil.formatDateStr(new Date(), "yyyy-MM-dd HH:mm:ss") + "*******************************同步企业号结束,耗时："+(System.currentTimeMillis() - start)/1000+"s------------------");
    }
    
    public void registIMUser() throws FileNotFoundException, IOException{
    	List<Map<String, Object>> list=getDataBaseDao().getJdbcTemplate().queryForList("select distinct * from usr_mapp.v_userinfo1 k ");
    	for (Map<String, Object> m : list) {
    		final String useraccount = (String) m.get("useraccount");
    		String ogrgid = (String) m.get("ogrgid");
			ogrgid=StringUtils.isBlank(ogrgid)?"000000":ogrgid;//没有机构的人员直接在顶级机构下
			String orgname = (String) m.get("orgname");
			orgname=StringUtils.isBlank(orgname)?p.getAppProperties("projectName"):orgname;//没有机构的人员直接在顶级机构下
			final String orgName=orgname;
			final String username = (String) m.get("username");
			Thread t=new Thread(){
				   public void run(){
					   //注册环信IM
					   System.out.println("--------环信注册： "+username+"------------");
					   registService.registIMUser(useraccount, username + "_" + orgName);
				   }
				};
				t.start();
		}
    }

	public IDataBaseDao getDataBaseDao() {
		return dataBaseDao;
	}

	public void setDataBaseDao(IDataBaseDao dataBaseDao) {
		this.dataBaseDao = dataBaseDao;
	}

	public JdbcTemplate getJdbcTemplateSLMiddle() {
		return jdbcTemplateSLMiddle;
	}

	public void setJdbcTemplateSLMiddle(JdbcTemplate jdbcTemplateSLMiddle) {
		this.jdbcTemplateSLMiddle = jdbcTemplateSLMiddle;
	}
	
}
